﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SMSModels;

namespace DAL.GoodsManager
{
    public class OutStoreService
    {
        

        /// <summary>
        /// 获取出库表数据集对象
        /// </summary>
        /// <returns>返回匹配的数据集对象</returns>
        public DataSet GetDataSetFromOutStoreTable()
        {
            string sql = "select 出库编号,出库表.货物编号,货物名称,出库表.仓库ID,仓库名称 as 所属仓库,出库表.供货商ID,供货商名称,规格,计量单位,出库方式,出库单价,出库数量,出库总金额,提货单位,提货人,出库时间,经手人,出库表.备注 from (select 货物编号,货物名称,供货商信息表.供货商名称,规格,计量单位,单价 from 货物档案表,供货商信息表 where 货物档案表.供货商ID=供货商信息表.供货商ID) a,出库表,仓库信息表 where 出库表.货物编号=a.货物编号 and 出库表.仓库ID=仓库信息表.仓库ID";
            string tbName = "outStoreInfo";
            return SQLHelper.GetDataSet(sql, tbName);
        }





        /// <summary>
        /// 出库前判断库存中货物是否充足
        /// </summary>
        /// <param name="outStore">出库对象</param>
        /// <returns>充足返回true，否则返回false</returns>
        public bool IsEnough(OutStore outStore)
        {
            string sqlIsEnough = string.Format("select 库存数量 from 库存表 where 货物编号='{0}' and 仓库ID={1}", outStore.GoodsID, outStore.StoreID);
            int a = Convert.ToInt32(SQLHelper.GetSingleResult(sqlIsEnough));
            if (a >= outStore.OutStoreQuantity)    //充足
            {
                return true;
            }
            else    //不充足
            {
                return false;
            }
        }


        /// <summary>
        /// 货物出库
        /// </summary>
        /// <param name="outStore"></param>
        /// <returns></returns>
        public int OutStore(OutStore outStore)
        {
            //[1]封装SQL语句，出库
            string sql = string.Format("insert into 出库表 (出库编号,货物编号,仓库ID,供货商ID,出库单价,出库数量,出库总金额,出库时间,出库方式,提货单位,提货人,经手人,备注) values('{0}','{1}',{2},{3},{4},{5},{6},'{7}','{8}','{9}','{10}','{11}','{12}')", outStore.OutStoreID, outStore.GoodsID, outStore.StoreID, outStore.SupplierID, outStore.OutStoreMonovalent, outStore.OutStoreQuantity, outStore.OutStoreTotal, outStore.OutStoreTime, outStore.OutStoreType, outStore.PickUpUnit, outStore.Consignee, outStore.Handler, outStore.Remarks);

            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 出库时更新库存数量
        /// </summary>
        /// <param name="outStore"></param>
        /// <returns></returns>
        public int UpdateStorageOnOutStore(OutStore outStore)
        {
            //[1]封装SQL语句
            string sqlUpdateStorage = string.Format("update 库存表 SET 库存数量=库存表.库存数量-{0} where 库存表.货物编号='{1}' and 仓库ID={2}", outStore.OutStoreQuantity, outStore.GoodsID, outStore.StoreID);
            //提交  更新库存
            return SQLHelper.Update(sqlUpdateStorage);

        }


        /// <summary>
        /// 修改出库信息
        /// </summary>
        /// <param name="outStore"></param>
        /// <returns></returns>
        public int ReviseOutStoreInfo(OutStore outStore)
        {
            //[1]封装SQL语句，修改出库信息
            string sql = string.Format("update 出库表 set 货物编号='{0}',仓库ID={1},供货商ID={2},出库单价={3},出库数量={4},出库总金额={5},出库时间='{6}',出库方式='{7}',提货单位='{8}',提货人='{9}',经手人='{10}',备注='{11}' where 出库编号='{12}'", outStore.GoodsID, outStore.StoreID, outStore.SupplierID, outStore.OutStoreMonovalent, outStore.OutStoreQuantity, outStore.OutStoreTotal, outStore.OutStoreTime, outStore.OutStoreType, outStore.PickUpUnit, outStore.Consignee, outStore.Handler, outStore.Remarks, outStore.OutStoreID);

            //[2]提交修改
            return SQLHelper.Update(sql);
        }


        /// <summary>
        /// 删除出库信息
        /// </summary>
        /// <param name="outStore"></param>
        /// <returns></returns>
        public int DeleteOutStoreInfo(OutStore outStore)
        {
            //[1]封装SQL语句，删除出库信息
            string sql = string.Format("delete from 出库表 where 出库编号='{0}'", outStore.OutStoreID);

            //[2]提交删除
            return SQLHelper.Update(sql);

        }
    }
}
